from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
from model import Book, Hero

# 1. 链接数据库等准备工作
engine = create_engine('mysql+pymysql://root:python@localhost:3306/19python', echo=True)
DBSession = sessionmaker(bind=engine)
session = DBSession()

# 2. 查询数据

# --- 基本查询 --
# 查询id为1的书和书里所有的英雄
# 查询id为1的书
print('-------------1---------------')
book = session.query(Book).filter(Book.id == 1).one()
print(book.name)
# 获取所有的英雄
heros = book.heros
for hero in heros:
    print(hero.name)

# 查询id为7的英雄和对应的书
# 查询id为7的英雄
print('-------------2---------------')
hero = session.query(Hero).filter(Hero.id == 7).one()
print(hero.name)
# 对应的书
book = hero.book
print(hero.book.name)

# --- 内连接查询 ---
# 查询有关联的图书
print('-------------3---------------')
books = session.query(Book).join(Hero, Book.id == Hero.book_id).all()
for book in books:
    print(book.name)

# 查询有关联的英雄
print('-------------4---------------')
heros = session.query(Hero).join(Book, Book.id == Hero.book_id).filter(Book.id > 1).all()

for hero in heros:
    print(hero.name)

# join(Book,Book.id == Hero.book_id)里的Book.id == Hero.book_id可以不写
# 默认就是用外键相等，如果默认on语句条件不符合要求，自己再添加
print('-------------5---------------')
objs = session.query(Hero).join(Book).filter(Hero.id > 1).all()
for hero in heros:
    print(hero.name)

print('-------------6---------------')
objs = session.query(Book, Hero).join(Book).all()
print(objs)  # 得到的是一个二维的元组 每个元组有一本图书和一个英雄
for obj in objs:
    print(obj[0].name, obj[1].name)

# --- 左连接查询 ---
# 查询有关联的图书
print('-------------7---------------')
objs = session.query(Book, Hero).outerjoin(Hero).all()  # 以book为左表
for obj in objs:
    print(obj[0].name if obj[0] else 'null', obj[1].name if obj[1] else 'null')

# 查询有关联的图书
print('-------------8---------------')
objs = session.query(Book, Hero).outerjoin(Book).all()  # 以hero为左表
for obj in objs:
    print(obj[0].name if obj[0] else 'null', obj[1].name if obj[1] else 'null')

# 4. 关闭session
session.close()
